package com.guhappy.util.business;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.guhappy.action.model.RollingStockBean;
import com.guhappy.action.model.Stock;

import edu.emory.mathcs.backport.java.util.Collections;


public class BusinessUtil {
	//This is synched so we only do one request at a time
    //If yahoo doesn't return stock info we will try to return it from the map in memory
	public static List<Stock>regionListUtil(String regionindex){
		List<Stock> regionlist = new ArrayList<Stock> ();
		Connection oraCon = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs = null;
		if (null != regionindex && !regionindex.trim().equals(""))
		{
			if (regionindex.equals("3"))
				sql.append("select ticker, name from ref_regionlist where regionindex = 1 or regionindex = 2");
			else
				sql.append("select ticker, name from ref_regionlist where regionindex = " ).append(Integer.parseInt(regionindex));
		}
		try {
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
			} 
			catch (ClassNotFoundException ex)
				{}
			oraCon = DriverManager.getConnection("jdbc:oracle:thin:@99.237.162.241:1521:orcl", "guhappy","guhappy");
			oraCon.setAutoCommit(false);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		try {
			Statement st = oraCon.createStatement();
			rs = st.executeQuery(sql.toString());
			while(rs.next()){
				regionlist.add(new Stock(rs.getString(1),rs.getString(2)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		Collections.sort(regionlist);
		return regionlist;
	}
	
	public static List<RollingStockBean>stockListUtil(String regionindex){
		List<RollingStockBean> stock = new ArrayList<RollingStockBean> ();
		Connection oraCon = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs = null;
		
		if (null != regionindex && !regionindex.trim().equals(""))
		{
			if (regionindex.equals("3"))
				sql.append("select ticker, CHANGE_PERCENTAGE from (select rownum, a.ticker, b.CHANGE_PERCENTAGE from (select ticker, name from ref_regionlist where REGIONINDEX = 1 or regionindex = 2) a left join global_stocks b on a.ticker = b.stock_ticker order by OUTSTANDING_SHARES/(volume+1) asc ) where rownum<100");
			else
				sql.append("select ticker, CHANGE_PERCENTAGE from (select rownum, a.ticker, b.CHANGE_PERCENTAGE from (select ticker, name from ref_regionlist where REGIONINDEX = " ).append(Integer.parseInt(regionindex)).append(" )a left join global_stocks b on a.ticker = b.stock_ticker order by OUTSTANDING_SHARES/(volume+1) asc ) where rownum<100");
		}
		try {
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
			} 
			catch (ClassNotFoundException ex)
				{}
			oraCon = DriverManager.getConnection("jdbc:oracle:thin:@99.237.162.241:1521:orcl", "guhappy","guhappy");
			oraCon.setAutoCommit(false);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		try {
			Statement st = oraCon.createStatement();
			rs = st.executeQuery(sql.toString());
			while(rs.next()){
				if (rs.getString(1)!=null && rs.getString(2)!=null)
					stock.add(new RollingStockBean(rs.getString(1),rs.getString(2)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stock;
	}
}
